/////// This code cleans the DBRS and Green Street Advisor data used in Jiang, Matvos, Piskorski, and Seru (2025)  //////
// the data is licensed and thus is not included in our replication package //
global raw "Replication_Package\Data\raw\"
global processed "Replication_Package\Data\processed\"
global output "Replication_Package\Output\"


///////////////////////////////////////////////////////////
// clean price index 
import excel "${raw}GreenStreet - CPPI.xlsx", sheet("CPPI") cellrange(A3:BZ56) firstrow clear
reshape long Q, i(Market CBSA) j(date)
ren Q index
gen quarter = int(date/100)
gen year = 2000+(date-quarter*100)
drop date 
gen date = yq(year, quarter)
format date %tq
gen property_type = "Apartment"

save "${processed}cre_price_index",replace 


import excel "${raw}GreenStreet - CPPI.xlsx", sheet("CPPI") cellrange(A59:BZ112) firstrow clear
reshape long Q, i(Market CBSA) j(date)
ren Q index
gen quarter = int(date/100)
gen year = 2000+(date-quarter*100)
drop date 
gen date = yq(year, quarter)
format date %tq
gen property_type = "Industrial"
append using "${processed}cre_price_index"
save "${processed}cre_price_index",replace 


import excel "${raw}GreenStreet - CPPI.xlsx", sheet("CPPI") cellrange(A115:BZ168) firstrow clear
reshape long Q, i(Market CBSA) j(date)
ren Q index
gen quarter = int(date/100)
gen year = 2000+(date-quarter*100)
drop date 
gen date = yq(year, quarter)
format date %tq
gen property_type = "Office"
append using "${processed}cre_price_index"
save "${processed}cre_price_index",replace 


import excel "${raw}GreenStreet - CPPI.xlsx", sheet("CPPI") cellrange(A171:BZ224) firstrow clear
reshape long Q, i(Market CBSA) j(date)
ren Q index
gen quarter = int(date/100)
gen year = 2000+(date-quarter*100)
drop date 
gen date = yq(year, quarter)
format date %tq
gen property_type = "Self Storage"
append using "${processed}cre_price_index"
save "${processed}cre_price_index",replace 

import excel "${raw}GreenStreet - CPPI.xlsx", sheet("CPPI") cellrange(A227:BZ280) firstrow clear
reshape long Q, i(Market CBSA) j(date)
ren Q index
gen quarter = int(date/100)
gen year = 2000+(date-quarter*100)
drop date 
gen date = yq(year, quarter)
format date %tq
gen property_type = "Senior Housing"
append using "${processed}cre_price_index"
save "${processed}cre_price_index",replace 

import excel "${raw}GreenStreet - CPPI.xlsx", sheet("CPPI") cellrange(A283:BZ311) firstrow clear
reshape long Q, i(Market CBSA) j(date)
ren Q index
gen quarter = int(date/100)
gen year = 2000+(date-quarter*100)
drop date 
gen date = yq(year, quarter)
format date %tq
gen property_type = "Single-Family Rental"
append using "${processed}cre_price_index"
save "${processed}cre_price_index",replace 

import excel "${raw}GreenStreet - CPPI.xlsx", sheet("CPPI") cellrange(A314:BZ367) firstrow clear
reshape long Q, i(Market CBSA) j(date)
ren Q index
gen quarter = int(date/100)
gen year = 2000+(date-quarter*100)
drop date 
gen date = yq(year, quarter)
format date %tq
gen property_type = "Strip Center"
append using "${processed}cre_price_index"
save "${processed}cre_price_index",replace 

use "${processed}cre_price_index",clear 
drop if missing(CBSA)
collapse index, by(CBSA  year quarter date)
gen property_type = "Average"
append using "${processed}cre_price_index"
drop if missing(CBSA)
collapse index, by(CBSA property_type year quarter date)
save "${processed}cre_price_index",replace
///////////////////////////////////////////////////////////////////
// clean loan price 
import excel "C:\Users\xuewe\Dropbox\Bank rate crisis\CRE\Data\raw\cbsa_name_code.xlsx", sheet("Sheet1") firstrow clear
replace Metropolitanareaname = subinstr(Metropolitanareaname, " (new name)", "",1)
replace Metropolitanareaname = subinstr(Metropolitanareaname, " (updated name)", "",1)
replace Metropolitanareaname = subinstr(Metropolitanareaname, " ('Urban Honolulu')", "",1)
save "${processed}cbsa_name",replace 

import excel "${raw}ZIP_CBSA_092023.xlsx", sheet("Export Worksheet") firstrow clear
sort ZIP TOT_RATIO
by ZIP: keep if _n==_N
keep ZIP CBSA
save "${processed}cbsa_zip",replace 

 import delimited "C:\Users\xuewe\Dropbox\Shared Datasets\DBRS Morningstar Loans\2023 vintage.csv", clear 
drop  losstotrust   crecloadddate appraisedvalueasofdate prospectusid  precedingasofdate  sponsor interestratetype indexname latestcommentarydate watchlistcode
 tostring zipcode,replace
  tostring delinquentdescription,replace
 destring precedingdscr crecloexitdate futurefundingamount  interestratefloor  interestratecap  caprateiss appraisedvalueiss ïviewpointloanid ioperiod currentpercentageofdeal,replace force
 save "${processed}cre_loan",replace 

forvalue x = 2000(1)2008{
 import delimited "C:\Users\xuewe\Dropbox\Shared Datasets\DBRS Morningstar Loans\\`x' vintage.csv", clear 
drop losstotrust    crecloadddate appraisedvalueasofdate prospectusid  precedingasofdate  sponsor interestratetype indexname latestcommentarydate watchlistcode
  tostring zipcode,replace
  tostring delinquentdescription,replace
 destring precedingdscr crecloexitdate futurefundingamount  interestratefloor  interestratecap caprateiss appraisedvalueiss ïviewpointloanid ioperiod currentpercentageofdeal,replace force

 append using "${processed}cre_loan"
save "${processed}cre_loan",replace 
}


forvalue x = 2011(1)2022{
 import delimited "C:\Users\xuewe\Dropbox\Shared Datasets\DBRS Morningstar Loans\\`x' vintage.csv", clear 
drop losstotrust    crecloadddate appraisedvalueasofdate prospectusid  precedingasofdate  sponsor interestratetype indexname latestcommentarydate watchlistcode
  tostring zipcode,replace
  tostring delinquentdescription,replace
 destring precedingdscr crecloexitdate  futurefundingamount  interestratefloor  interestratecap caprateiss appraisedvalueiss ïviewpointloanid ioperiod currentpercentageofdeal,replace force
 append using "${processed}cre_loan"
save "${processed}cre_loan",replace 
}

use  "${processed}cre_price_index",clear 
keep CBSA 
duplicates drop
save "${processed}cbsa",replace 

use "${processed}cre_loan",clear 
drop if strpos(status,"Loss")>1 // | strpos(status, "Defeased")
ren zipcode ZIP
merge m:1 ZIP using "${processed}cbsa_zip"
drop if _merge ==2
drop _merge 
destring CBSA,replace
replace CBSA = 99999 if missing(CBSA)
merge m:1 CBSA using "${processed}cbsa"
replace CBSA = 99999 if _merge ==1
drop if _merge ==2 
drop _merge 

replace propertytype=trim(propertytype)
gen property_type = "Apartment" if propertytype=="Multifamily" |propertytype=="Coop" |propertytype=="MHC"| propertytype=="RV Park" |propertytype=="Student Housing"    
replace property_type = "Senior Housing" if propertytype=="Assisted Living" | propertytype=="Independent Living" 
replace property_type = "Industrial" if propertytype=="Industrial" | propertytype=="Automotive Dealership" | propertytype=="Health Care" | propertytype=="Land"     | propertytype=="Flex (Industrial Office)" 
replace property_type = "Office" if propertytype=="Office" | propertytype=="Leased Fee" 
replace property_type = "Self Storage" if propertytype=="Self Storage"
replace property_type = "Strip Center" if strpos(propertytype, "Mall")>0 | strpos(propertytype, "Retail")>0 | strpos(propertytype, "Anchored")>0
replace property_type = "Average" if missing(property_type)

gen refi_rate = (6.42+6.97)/2 if property_type=="Strip Center"
replace refi_rate = (6.32+5.97)/2 if property_type=="Apartment" 
replace refi_rate = 6.47 if property_type == "Self Storage" 
replace refi_rate = 6.57 if property_type == "Industrial" 
replace refi_rate = 7.42 if property_type=="Office" 
replace refi_rate = 7.97 if strpos(propertytype, "Hotel")>0
replace refi_rate = (6.42+6.97+6.32+5.97+6.47+6.57+7.42+7.97)/8 if missing(refi_rate)


ren ïviewpointloanid loan_id
gen year = substr(originationdate,-4,4)
gen month = substr(originationdate,1,2)
replace month = subinstr(month, "/","",1)
destring year month,replace 
gen quarter = 1 if month<4
replace quarter = 2 if month>=4 & month<7
replace quarter = 3 if month>=7 & month<10
replace quarter = 4 if month>=10

merge m:1 CBSA property_type year quarter using  "${processed}cre_price_index"
keep if _merge==3
drop _merge 

drop year quarter 
ren index origin_index

gen year = 2023
gen quarter = 4 
merge m:1 CBSA property_type year quarter using  "${processed}cre_price_index"
keep if _merge==3
drop _merge 

replace refi_rate =refi_rate/100
gen current_ltv = currenttrustbalance/(appraisedvalueiss*index/origin_index)
gen underwater = (current_ltv>1)
gen dscr_refi_v1 = issuerdscr*(precedingncf/issuerncf)/((currenttrustbalance*refi_rate)/(trustbalanceiss*interestrate))

gen payment_refi = currenttrustbalance/((1-1/(1+refi_rate)^25)/refi_rate)
gen payment_issue =trustbalanceiss/((1-1/(1+interestrate)^25)/interestrate)
gen payment_ratio = payment_refi/payment_issue 
gen dscr_refi_v2 = precedingdscr/payment_ratio 

replace amortizationtype=trim(amortizationtype)

gen dscr_refi = dscr_refi_v2  if strpos(amortizationtype,"Amortizing")>0
replace dscr_refi = dscr_refi_v1 if missing(dscr_refi) 
 
save "${processed}cre_loan_clean",replace 


//////////////// creating pseudo data ////////////////

use "${processed}cre_loan_clean",clear 
sample 10
replace loan_id = _n 
keep loan_id property_type currenttrustbalance current_ltv precedingdscr dscr_refi maturitydate propertytype ltviss interestrate refi_rate issuerdscr

order property_type maturitydate loan_id 
foreach x of var currenttrustbalance precedingdscr current_ltv dscr_refi ltviss  interestrate refi_rate issuerdscr {
egen mean = mean(`x')
egen sd = sd(`x')	
replace `x' = rnormal(mean,sd)
drop mean sd
replace `x' = -`x' if `x'<0
}

save "${processed}cre_loan_pseudo",replace


